﻿from firebird import aview

class ArticlesToImport(aview):
	"This class is a manipulator for articles to import view in firebird"
	#beginning of class suite
	#print (self.__doc__)
	def __init__(self):
		super(ArticlesToImport, self).__init__()
		self.name='ARTICLES_TO_IMPORT'
		self.fields=['art_id', 'section_id', 'sectname', 'doc_type', 'doc_name', 'designatio', 'name', 'note', 'mu_id', 'purchased', 'format', 'modifdate', 'version_id']
		self.query='''select 

DISTINCT 

a.art_id, a.section_id, ss.sectname, d.doc_type, e.doc_name, a.designatio, a.name, a.note, a.mu_id, a.purchased, d.format, a.modifdate, d.version_id

     FROM articles a, articles_view av, archives ar, pc, ssections ss, doclist d, doctypes e

          WHERE
               
               e.doc_type=d.doc_type
      
               AND d.doc_id=a.doc_id
               
               AND a.section_id=ss.section_id

               AND extract(YEAR from a.chkindate)>2005

               AND a.art_id>0

               AND ar.archive_id=av.archive_id

               AND (ar.archive_id=31
                        OR ar.archive_id=34
                        OR ar.archive_id=35
                        OR ar.archive_id=36
                        OR ar.archive_id=37
                        OR ar.archive_id=38
                        OR ar.archive_id=41
                        OR ar.archive_id=42
                        OR ar.archive_id=43)

               AND CHAR_LENGTH(TRIM(a.name))>0

               AND 

                (
                 (a.art_id=pc.part_aid
                  OR a.art_id=pc.proj_aid)

                 AND av.art_id=pc.proj_aid
                 AND CHAR_LENGTH(TRIM(a.designatio))>0
                 AND (a.SECTION_ID=1
                          OR a.SECTION_ID=3
                          OR a.SECTION_ID=4
                          OR a.SECTION_ID=8)
                )

UNION ALL

select 

DISTINCT 

a.art_id, a.section_id, ss.sectname, d.doc_type, e.doc_name, a.designatio, a.name, a.note, a.mu_id, a.purchased, d.format, a.modifdate, d.version_id

     FROM 

articles a, articles_view av, archives ar, pc, ssections ss, doclist d, doctypes e

          WHERE
               
               e.doc_type=d.doc_type
      
               AND d.doc_id=a.doc_id

               AND a.section_id=ss.section_id

               AND extract(YEAR from a.chkindate)>2005

               AND a.art_id>0

               AND ar.archive_id=av.archive_id

               AND (ar.archive_id=31
                        OR ar.archive_id=34
                        OR ar.archive_id=35
                        OR ar.archive_id=36
                        OR ar.archive_id=37
                        OR ar.archive_id=38
                        OR ar.archive_id=41
                        OR ar.archive_id=42
                        OR ar.archive_id=43)

               AND CHAR_LENGTH(TRIM(a.name))>0

               AND 
                
                (
                 a.art_id=pc.part_aid
                 AND av.art_id=pc.proj_aid
                 AND CHAR_LENGTH(TRIM(a.designatio))=0
                 AND (a.SECTION_ID=5
                          OR a.SECTION_ID=6
                          OR a.SECTION_ID=7)
                )'''

	#end of ArticlesToImport class suite

from mysql import aview as aview1

class accs(aview1):
	"This class is a manipulator for articles to import view in firebird"
	#beginning of class suite
	#print (self.__doc__)
	def __init__(self):
		super(accs, self).__init__()
		self.name='accs'
		self.fields=['*']
		self.query='''select * from 1s_1saccs'''

	#end of ArticlesToImport class suite

class OraKobjToImport:
	"This class is a manipulator for konstrobj to import view"
	#beginning of class suite
	#print (OraKobjToImport.__doc__)
	
	name='X_OASUP_SO_KOBJ_TO_IMPORT'
	
	def create():
		'''Function creates the view'''
		#beginning of function suite
		#print(OraKobjToImport.create.__doc__)
		
		name=OraKobjToImport.name
		#Выполняется подключение к базе данных Oracle
		db.oracle.connect()
		
		#Выполняется транзакция подсчета количества видов с именем вида в каталоге видов
		db.oracle.cursor.execute("SELECT COUNT(*) FROM USER_VIEWS WHERE VIEW_NAME=:VIEW_NAME",VIEW_NAME=name)
		#Количество видов с именем вида присваивается переменной before
		before=db.oracle.cursor.fetchall()[0][0]
		if before==0:
		
			# Выполняется запрос на создание вида в Oracle:
			db.oracle.cursor.execute(''' CREATE OR REPLACE NOFORCE VIEW "OMP_ADM"."'''+name+'''" 

			("UNVCODE", "REVISION", "ITEMTYPE", "ITEMTYPE_NAME", "SIGN", "NAME", "NOTICE", "MEASCODE", "MEASNAME", "SUPPLYTYPE", "FORMAT", "OWNER", "OWNER_NAME", "RECDATE", "PRODCODE", "OMP_OBJECTS_CODE", "FIRST_PROMCODE", "LAST_PROMCODE", "CURRENT_STATE", "STATEDATE", "LASTNAME") 
					
					AS 
					
					SELECT a.UNVCODE, a.REVISION, a.ITEMTYPE, b."NAME" AS "ITEMTYPE_NAME", a.SIGN,
a."NAME", a.NOTICE, a.MEASCODE, c."NAME" as MEASNAME, a.SUPPLYTYPE, a.FORMAT, 
a.OWNER, d."NAME" as "OWNER_NAME", a.RECDATE, a.PRODCODE, e.CODE as "OMP_OBJECTS_CODE",
g.CODE as "FIRST_PROMCODE", h.CODE as "LAST_PROMCODE", i.CURRENT_STATE, i.STATEDATE, i.LASTNAME


FROM OMP_ADM.KONSTROBJ a, OMP_ADM.KO_TYPES b, 
    OMP_ADM.MEASURES c, OMP_ADM.OWNER_NAME d,
    OMP_ADM.BUSINESS_OBJECTS e,
    
    (SELECT a.DOCCODE as "UNVCODE", MIN(b.CODE) AS "CODE"
        FROM OMP_ADM.BUSINESS_OBJECTS a, OMP_ADM.BUSINESSOBJ_PROMOTION b, OMP_ADM.BUSINESSOBJ_STATES c
            WHERE b.BUSINESSOBJ=a.CODE
                AND c.CODE=b.CURRENT_STATE
                AND a."TYPE" IN (0,1,2,3,4,5,6,22)
        GROUP BY a.DOCCODE) g,

    (SELECT a.DOCCODE as "UNVCODE", MAX(b.CODE) AS "CODE"
        FROM OMP_ADM.BUSINESS_OBJECTS a, OMP_ADM.BUSINESSOBJ_PROMOTION b, OMP_ADM.BUSINESSOBJ_STATES c
            WHERE b.BUSINESSOBJ=a.CODE
                AND c.CODE=b.CURRENT_STATE
                AND a."TYPE" IN (0,1,2,3,4,5,6,22)
        GROUP BY a.DOCCODE) h,

    (SELECT a.CODE, b."NAME" as "CURRENT_STATE", a.STATEDATE, a.LASTNAME 
        FROM OMP_ADM.BUSINESSOBJ_PROMOTION a, OMP_ADM.BUSINESSOBJ_STATES b
            WHERE b.CODE=a.CURRENT_STATE) i

            
            WHERE b.CODE=a.ITEMTYPE
                AND c.CODE=a.MEASCODE
                AND d.OWNER=a.OWNER
                AND e.DOCCODE=a.UNVCODE
                AND e."TYPE"=a.ITEMTYPE
                AND g.UNVCODE=a.UNVCODE
                AND h.UNVCODE=a.UNVCODE
                AND h.CODE=i.CODE
                AND a.ITEMTYPE IN (0,1,2,3,4,5,6,22)''')

		#Выполняется транзакция подсчета количества видов с именем вида в каталоге видов
		db.oracle.cursor.execute("SELECT COUNT(*) FROM USER_VIEWS WHERE VIEW_NAME=:VIEW_NAME",VIEW_NAME=name)
		#Количество видов с именем вида присваивается переменной after
		after=db.oracle.cursor.fetchall()[0][0]
		#Закрывается подключение к базе данных Oracle
		db.oracle.disconnect()
		#end of function suite
		return after-before

	def drop():
		'''Function drops the view'''
		#beginning of function suite
		#print(OraKobjToImport.drop.__doc__)
		
		name=OraKobjToImport.name
		#Выполняется подключение к базе данных
		db.oracle.connect()
		
		#Выполняется транзакция подсчета количества видов с именем вида в каталоге видов
		db.oracle.cursor.execute("SELECT COUNT(*) FROM USER_VIEWS WHERE VIEW_NAME=:VIEW_NAME",VIEW_NAME=name)
		#Количество видов с именем вида присваивается переменной before
		before=db.oracle.cursor.fetchall()[0][0]
		if before!=0:
			# Выполняется запрос на уничтожение вида:
			db.oracle.cursor.execute('''DROP VIEW "OMP_ADM"."'''+name+'''"''')
		#Выполняется транзакция подсчета количества видов с именем вида в каталоге видов
		db.oracle.cursor.execute("SELECT COUNT(*) FROM USER_VIEWS WHERE VIEW_NAME=:VIEW_NAME",VIEW_NAME=name)
		#Количество видов с именем вида присваивается переменной after
		after=db.oracle.cursor.fetchall()[0][0]
		#Закрывается подключение к базе данных
		db.oracle.disconnect()
		#end of function suite
		return before-after

#end of OraKobjToImport class suite